using System;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Web;
using MySql.Data.MySqlClient;
namespace LdExpress
{
    /// <summary>
    /// 
    /// </summary>
    public class TruckerPrice
    {
        public long ID = 0;
        public string Cont20 = "";
        public string Tons20 = "";
        public string Kms20 = "";
        public string DoPrice20 = "";
        public string Note20 = "";
        public DateTime LastUdate20 = DateTime.Now;
        public string Cont40 = "";
        public string Tons40 = "";
        public string Kms40 = "";
        public string DoPrice40 = "";
        public string Note40 = "";
        public string LastUdate40 = "";
        public long TruckerID = 0;
        public long ExID = 0;
        public long ToID = 0;
        public TruckerPrice()
        {
        }
        public TruckerPrice(long lID)
        {
            this.ID = lID;
        }
        public void Save(MySqlConnection cn, MySqlTransaction trans)
        {
            string sqlCommand = "";
            if (ID <= 0) // new object, we insert new record to database
            {
                // build SQL statement
                sqlCommand = "Insert into tbl_TruckerPrice (" +
                             "Cont20," +
                             "Tons20," +
                             "Kms20," +
                             "DoPrice20," +
                             "Note20," +
                             "LastUdate20," +
                             "Cont40," +
                             "Tons40," +
                             "Kms40," +
                             "DoPrice40," +
                             "Note40," +
                             "LastUdate40," +
                             "TruckerID," +
                             "ExID," +
                             "ToID) " +
                         " values(" +
                             "'" + DBModule.RefineString(Cont20) + "'," +
                             "'" + DBModule.RefineString(Tons20) + "'," +
                             "'" + DBModule.RefineString(Kms20) + "'," +
                             "'" + DBModule.RefineString(DoPrice20) + "'," +
                             "'" + DBModule.RefineString(Note20) + "'," +
                             DBModule.RefineDatetime(LastUdate20) + "," +
                             "'" + DBModule.RefineString(Cont40) + "'," +
                             "'" + DBModule.RefineString(Tons40) + "'," +
                             "'" + DBModule.RefineString(Kms40) + "'," +
                             "'" + DBModule.RefineString(DoPrice40) + "'," +
                             "'" + DBModule.RefineString(Note40) + "'," +
                             "'" + DBModule.RefineString(LastUdate40) + "'," +
                             TruckerID.ToString() + "," +
                             ExID.ToString() + "," +
                             ToID.ToString() + ")";
                // run SQL statement
                DBModule.ExecuteNonQuery(sqlCommand, cn, trans);
                UserObjectsLog.SaveObjectActionLog("TruckerPrice", "add", cn, trans);                
            }
            else // edit object, we update old record in database
            {
                // build SQL statement
                sqlCommand = "Update tbl_TruckerPrice set " +
                             "Cont20='" + DBModule.RefineString(Cont20) + "'," +
                             "Tons20='" + DBModule.RefineString(Tons20) + "'," +
                             "Kms20='" + DBModule.RefineString(Kms20) + "'," +
                             "DoPrice20='" + DBModule.RefineString(DoPrice20) + "'," +
                             "Note20='" + DBModule.RefineString(Note20) + "'," +
                             "LastUdate20=" + DBModule.RefineDatetime(LastUdate20) + "," +
                             "Cont40='" + DBModule.RefineString(Cont40) + "'," +
                             "Tons40='" + DBModule.RefineString(Tons40) + "'," +
                             "Kms40='" + DBModule.RefineString(Kms40) + "'," +
                             "DoPrice40='" + DBModule.RefineString(DoPrice40) + "'," +
                             "Note40='" + DBModule.RefineString(Note40) + "'," +
                             "LastUdate40='" + DBModule.RefineString(LastUdate40) + "'," +
                             "TruckerID=" + TruckerID.ToString() + "," +
                             "ExID=" + ExID.ToString() + "," +
                             "ToID=" + ToID.ToString() + "" +
                    " where ID=" + ID.ToString();
                // run SQL statement
                DBModule.ExecuteNonQuery(sqlCommand, cn, trans);
                UserObjectsLog.SaveObjectActionLog("TruckerPrice", "edit", cn, trans);                
            }
        }
        public void Delete(MySqlConnection cn, MySqlTransaction trans)
        {
            //Buil SQL Statement
            //Run SQL Statement
            string sqlCommand = "DELETE FROM tbl_TruckerPrice WHERE ID = " + this.ID.ToString() + " limit 1";
            DBModule.ExecuteNonQuery(sqlCommand, cn, trans);
            UserObjectsLog.SaveObjectActionLog("TruckerPrice", "delete", cn, trans);
        }
        public void Load(MySqlConnection cn, MySqlTransaction trans)
        {
            string sqlCommand = "";
            //Build SQL Statement
            //Run SQL Statement
            sqlCommand = "SELECT * FROM tbl_TruckerPrice WHERE ID = " + ID.ToString() + " limit 0,1";
            DataSet ds = DBModule.ExecuteQuery(sqlCommand, cn, trans);
            //Fill data into this object
            if (ds.Tables[0].Rows.Count > 0)
            {
                DataRow dr = ds.Tables[0].Rows[0];
                if (!dr.IsNull("ID"))
                    ID = long.Parse(dr["ID"].ToString());
                if (!dr.IsNull("Cont20"))
                    Cont20 = dr["Cont20"].ToString();
                if (!dr.IsNull("Tons20"))
                    Tons20 = dr["Tons20"].ToString();
                if (!dr.IsNull("Kms20"))
                    Kms20 = dr["Kms20"].ToString();
                if (!dr.IsNull("DoPrice20"))
                    DoPrice20 = dr["DoPrice20"].ToString();
                if (!dr.IsNull("Note20"))
                    Note20 = dr["Note20"].ToString();
                if (!dr.IsNull("LastUdate20"))
                    LastUdate20 = DateTime.Parse(dr["LastUdate20"].ToString());
                if (!dr.IsNull("Cont40"))
                    Cont40 = dr["Cont40"].ToString();
                if (!dr.IsNull("Tons40"))
                    Tons40 = dr["Tons40"].ToString();
                if (!dr.IsNull("Kms40"))
                    Kms40 = dr["Kms40"].ToString();
                if (!dr.IsNull("DoPrice40"))
                    DoPrice40 = dr["DoPrice40"].ToString();
                if (!dr.IsNull("Note40"))
                    Note40 = dr["Note40"].ToString();
                if (!dr.IsNull("LastUdate40"))
                    LastUdate40 = dr["LastUdate40"].ToString();
                if (!dr.IsNull("TruckerID"))
                    TruckerID = long.Parse(dr["TruckerID"].ToString());
                if (!dr.IsNull("ExID"))
                    ExID = long.Parse(dr["ExID"].ToString());
                if (!dr.IsNull("ToID"))
                    ToID = long.Parse(dr["ToID"].ToString());
            }
            else
            {
            }
        }
        public static void GetList(string Condition, string OrderBy, int NumOfItemsPerPage, int PageNo, out int NumberOfPages, out DataSet ds, MySqlConnection cn, MySqlTransaction trans)
        {
            NumberOfPages = 0;
            string sqlCommand = "";
            ds = null;
            // build SQL statement
            sqlCommand = "Select tbl_TruckerPrice.*,TRUCKER.BizName from tbl_TruckerPrice " +
                " left Join tbl_Trucker TRUCKER on tbl_TruckerPrice.TruckerID=TRUCKER.ID " +
                " where 1=1 ";
            if (Condition.Length > 0)
            {
                sqlCommand += Condition;
            }

            if ((OrderBy != null) && (OrderBy != ""))
                sqlCommand = sqlCommand + " Order By " + DBModule.RefineString(OrderBy);
            // run SQL statement
            if (NumOfItemsPerPage > 0)
            {
                ds = DBModule.ExecuteQuery(sqlCommand, NumOfItemsPerPage, PageNo, out NumberOfPages, cn, trans);
            }
            else
                ds = DBModule.ExecuteQuery(sqlCommand, cn, trans);
        }
        public static int GetListCount(string Condition, MySqlConnection cn, MySqlTransaction trans)
        {
            string sqlCommand = "";
            sqlCommand = "Select Count(ID) from tbl_TruckerPrice where 1=1 ";
            if (Condition.Length > 0)
                sqlCommand += Condition;
            string nCount = DBModule.ExecuteQueryForOneResult(sqlCommand, cn, trans);
            if (!String.IsNullOrEmpty(nCount))
                return int.Parse(nCount);
            else
                return 0;
        }
    }
}
